package com.demo.web;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.demo.dto.ComboBoxDto;
import com.demo.dto.TableStructureDiff;
import com.demo.dto.ValidationResult;
import com.demo.dto.ValidationStatistics;
import com.demo.enums.ErpOrDw;
import com.demo.model.base.ColumnBaseInfo;
import com.demo.model.base.ColumnBaseInfoExample;
import com.demo.model.base.TableColumnInfo;
import com.demo.model.base.TableColumnInfoExample;
import com.demo.model.base.TableRelation;
import com.demo.service.CommonDataService;
import com.demo.service.base.ColumnBaseInfoService;
import com.demo.service.base.TableColumnInfoService;
import com.demo.service.base.TableRelationService;
import com.demo.util.ExcelData;
import com.demo.util.ExcelUtil;
import com.google.common.base.Function;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.google.common.collect.Sets.SetView;

@Controller
@RequestMapping(value="")
public class ValidationController {

	@Autowired
	CommonDataService commonDataService;
	
	@Autowired
	ColumnBaseInfoService columnBaseInfoService;
	
	@Autowired
	TableColumnInfoService tableColumnInfoService;
	
	@Autowired
	TableRelationService tableRelationService;
	
	@ResponseBody
	@RequestMapping(value="dataCollection")
	public Object dataCollection(
			@RequestParam("dw")MultipartFile dw,
			@RequestParam("erp")MultipartFile erp,
			@RequestParam("tableName")String tableName){
		ValidationResult<Object> result = new ValidationResult<Object>();
		//总体思路是动态建立一张表，把两个excel里的数据都存进去，之后再分析这张表里的数据
		//同时 把两个excel里列名存在一张表里，区分共有列名和两个excel独有的列名
		if (dw != null && !dw.isEmpty() && erp != null && !erp.isEmpty() && StringUtils.isNotEmpty(tableName)) {
			try {
				ExcelData dwExcelData = ExcelUtil.getExcelData(dw.getInputStream());
				ExcelData erpExcelData = ExcelUtil.getExcelData(erp.getInputStream());
				if (null != dwExcelData && null != erpExcelData) {
					//开始处理列名
					Set<String> columnNameSet_dw = new HashSet<String>(dwExcelData.getColumnNameList());
					columnNameSet_dw.remove("FIN_UPDATE_TIME");
					columnNameSet_dw.remove("FIN_BATCH_NO");
					columnNameSet_dw.remove("FIN_BUSS_DATE");
					Set<String> columnNameSet_erp = new HashSet<String>(erpExcelData.getColumnNameList());
					SetView<String> intersection = Sets.intersection(columnNameSet_dw, columnNameSet_erp);
					SetView<String> difference_dw = Sets.difference(columnNameSet_dw, intersection);
					SetView<String> difference_erp = Sets.difference(columnNameSet_erp, intersection);
					
					Set<String> columnNameSet = new HashSet<String>(intersection);
					for (Iterator<String> iterator = difference_dw.iterator(); iterator.hasNext();) {
						String columnName = (String) iterator.next();
						columnNameSet.add(columnName);
					}
					for (Iterator<String> iterator = difference_erp.iterator(); iterator.hasNext();) {
						String columnName = (String) iterator.next();
						columnNameSet.add(columnName);
					}
					columnNameSet.add("dw_or_erp");
					//处理列名结束
					
					//将列名异同存到列名基础信息表里
					List<ColumnBaseInfo> columnBaseInfos = new ArrayList<ColumnBaseInfo>();
					for (Iterator<String> iterator = intersection.iterator(); iterator.hasNext();) {
						String columnName = (String) iterator.next();
						ColumnBaseInfo columnBaseInfo = new ColumnBaseInfo();
						columnBaseInfo.setErpOrDw(ErpOrDw.Both.getCode());
						columnBaseInfo.setTableName(tableName);
						columnBaseInfo.setColumnName(columnName);
						columnBaseInfos.add(columnBaseInfo);
					}
					for (Iterator<String> iterator = difference_erp.iterator(); iterator.hasNext();) {
						String columnName = (String) iterator.next();
						ColumnBaseInfo columnBaseInfo = new ColumnBaseInfo();
						columnBaseInfo.setErpOrDw(ErpOrDw.ERP.getCode());
						columnBaseInfo.setTableName(tableName);
						columnBaseInfo.setColumnName(columnName);
						columnBaseInfos.add(columnBaseInfo);
					}
					for (Iterator<String> iterator = difference_dw.iterator(); iterator.hasNext();) {
						String columnName = (String) iterator.next();
						ColumnBaseInfo columnBaseInfo = new ColumnBaseInfo();
						columnBaseInfo.setErpOrDw(ErpOrDw.DW.getCode());
						columnBaseInfo.setTableName(tableName);
						columnBaseInfo.setColumnName(columnName);
						columnBaseInfos.add(columnBaseInfo);
					}
					columnBaseInfoService.deleteAndAddBatch(tableName, columnBaseInfos);
					
					//动态创建excel对应的表结构，然后将两个excel的数据存到该表里
					List<Map<String, String>> columnValueList_dw = dwExcelData.getColumnValueList();
					List<Map<String, String>> columnValueList_erp = erpExcelData.getColumnValueList();
					List<Map<String, String>> columnValueList = new ArrayList<Map<String, String>>(columnValueList_dw.size()+columnValueList_erp.size());
					for (Iterator<Map<String, String>> iterator = columnValueList_dw.iterator(); iterator
							.hasNext();) {
						Map<String, String> map = (Map<String, String>) iterator.next();
						map.put("dw_or_erp", ErpOrDw.DW.getCode().toString());
					}
					for (Iterator<Map<String, String>> iterator = columnValueList_erp.iterator(); iterator
							.hasNext();) {
						Map<String, String> map = (Map<String, String>) iterator.next();
						map.put("dw_or_erp", ErpOrDw.ERP.getCode().toString());
					}
					columnValueList.addAll(columnValueList_dw);
					columnValueList.addAll(columnValueList_erp);
					
					boolean createTableFlag = commonDataService.createTable(tableName, columnNameSet);
					if (createTableFlag) {
						commonDataService.batchInsert(tableName, columnNameSet, columnValueList);
					}
					result.setSuccess(true);
				}
			} catch (IOException e) {
				result.setError(e.getMessage());
			}
		}
 		return result;
	}
	
	@ResponseBody
	@RequestMapping(value="dataAnalysis/{tableName}")
	public Object dataAnalysis(@PathVariable("tableName")String tableName){
		ValidationStatistics statistics = new ValidationStatistics();
		
		//统计哪些字段是ERP库和财务库对应表共有字段，哪些字段是独有的
		ColumnBaseInfoExample example = new ColumnBaseInfoExample();
		example.or().andTableNameEqualTo(tableName).andErpOrDwEqualTo(ErpOrDw.Both.getCode());
		List<ColumnBaseInfo> sharedColumns = columnBaseInfoService.findByCondition(example);
		statistics.setBothErpAndDw(Lists.transform(sharedColumns, new Function<ColumnBaseInfo, String>() {
			public String apply(ColumnBaseInfo columnBaseInfo) {
				return columnBaseInfo.getColumnName();
			}
		}));
		example.clear();
		example.or().andTableNameEqualTo(tableName).andErpOrDwEqualTo(ErpOrDw.ERP.getCode());
		statistics.setErpOnly(Lists.transform(columnBaseInfoService.findByCondition(example), new Function<ColumnBaseInfo, String>() {
			public String apply(ColumnBaseInfo columnBaseInfo) {
				return columnBaseInfo.getColumnName();
			}
		}));
		example.clear();
		example.or().andTableNameEqualTo(tableName).andErpOrDwEqualTo(ErpOrDw.DW.getCode());
		statistics.setDwOnly(Lists.transform(columnBaseInfoService.findByCondition(example), new Function<ColumnBaseInfo, String>() {
			public String apply(ColumnBaseInfo columnBaseInfo) {
				return columnBaseInfo.getColumnName();
			}
		}));
		
		//统计没有刷新更新时间的pkid
		statistics.setDiffIds(commonDataService.getDiffIds(tableName));
		
		//统计哪些字段修改了没有刷新更新时间
		List<String> sharedColumnNames = Lists.transform(sharedColumns, new Function<ColumnBaseInfo, String>() {
			public String apply(ColumnBaseInfo columnBaseInfo){
				return columnBaseInfo.getColumnName();
			}
		});
		statistics.setDiffMap(commonDataService.analyzeDiff(tableName,sharedColumnNames));
		
		return statistics;
	}
	
	@ResponseBody
	@RequestMapping(value="getComboOfTableName")
	public Object getComboOfTableName(){
		List<ComboBoxDto> list = columnBaseInfoService.getComboOfTableName();
		
		return list;
	}

	
	@ResponseBody
	@RequestMapping(value="tableRelationUpload")
	public Object tableRelationUpload(
			@RequestParam("relation")MultipartFile relation){
		ValidationResult<Object> result = new ValidationResult<Object>();
		if (relation != null && !relation.isEmpty()) {
			try {
				ExcelData relationExcelData = ExcelUtil.getExcelData(relation.getInputStream());
				if (null != relationExcelData) {
					List<Map<String, String>> columnValueList_relation = relationExcelData.getColumnValueList();
					List<TableRelation> list = new ArrayList<TableRelation>(columnValueList_relation.size());
					for (Iterator<Map<String, String>> iterator = columnValueList_relation.iterator(); iterator
							.hasNext();) {
						Map<String, String> map = iterator.next();
						TableRelation tableRelation = new TableRelation();
						tableRelation.setErpTableName(map.get("ERP"));
						tableRelation.setOdsTableName(map.get("ODS"));
						tableRelation.setDwTableName(map.get("DW"));
						list.add(tableRelation);
					}
					tableRelationService.addBatch(list);
					result.setSuccess(true);
				}
			} catch (IOException e) {
				result.setError(e.getMessage());
			}
		}
		return result;
	}
	
	@ResponseBody
	@RequestMapping(value="tableColumnInfoUpload")
	public Object tableColumnInfoUpload(
			@RequestParam("columnInfo")MultipartFile columnInfo,
			@RequestParam("onlineUatTest")Integer onlineUatTest){
		ValidationResult<Object> result = new ValidationResult<Object>();
		if (columnInfo != null && !columnInfo.isEmpty()) {
			try {
				ExcelData columnInfoExcelData = ExcelUtil.getExcelData(columnInfo.getInputStream());
				if (null != columnInfoExcelData) {
					List<Map<String, String>> columnValueList_columnInfo = columnInfoExcelData.getColumnValueList();
					List<TableColumnInfo> list = new ArrayList<TableColumnInfo>(columnValueList_columnInfo.size());
					for (Iterator<Map<String, String>> iterator = columnValueList_columnInfo.iterator(); iterator
							.hasNext();) {
						Map<String, String> map = iterator.next();
						if ("FIN_UPDATE_TIME".equalsIgnoreCase(map.get("COLUMN_NAME"))
								|| "FIN_BATCH_NO".equalsIgnoreCase(map.get("COLUMN_NAME"))
								|| "FIN_BUSS_DATE".equalsIgnoreCase(map.get("COLUMN_NAME"))) {
							continue;
						}
						TableColumnInfo tableColumnInfo = new TableColumnInfo();
						tableColumnInfo.setOnlineUatTest(onlineUatTest);
						tableColumnInfo.setTableName(map.get("TABLE_NAME"));
						tableColumnInfo.setColumnName(map.get("COLUMN_NAME"));
						tableColumnInfo.setDataType(map.get("DATA_TYPE"));
						tableColumnInfo.setDataLength(map.get("DATA_LENGTH"));
						tableColumnInfo.setDataPrecision(map.get("DATA_PRECISION"));
						tableColumnInfo.setDataDefault(map.get("DATA_DEFAULT"));
						tableColumnInfo.setNullable(map.get("NULLABLE"));
						list.add(tableColumnInfo);
					}
					tableColumnInfoService.addBatch(list);
					result.setSuccess(true);
				}
			} catch (IOException e) {
				result.setError(e.getMessage());
			}
		}
		return result;
	}
	
	@ResponseBody
	@RequestMapping(value="tableDiffAnalysis")
	public Object tableDiffAnalysis(
			@RequestParam(value="env_left",required=false,defaultValue = "1")Integer env_left,
			@RequestParam(value="tableName_left",required=false)String tableName_left,
			@RequestParam(value="env_right",required=false,defaultValue = "1")Integer env_right,
			@RequestParam(value="tableName_right",required=false)String tableName_right){
		ValidationResult<Object> result = new ValidationResult<Object>();
		ArrayList<TableStructureDiff> diffList = new ArrayList<TableStructureDiff>();
		if (StringUtils.isEmpty(tableName_left) || StringUtils.isEmpty(tableName_right)){
			List<TableRelation> list = tableRelationService.findByCondition(null);
			TableColumnInfoExample example_left = new TableColumnInfoExample();
			TableColumnInfoExample example_right = new TableColumnInfoExample();
			for (Iterator<TableRelation> iterator = list.iterator(); iterator.hasNext();) {
				TableStructureDiff diff = new TableStructureDiff();
				TableRelation tableRelation = (TableRelation) iterator.next();
				tableName_left = tableRelation.getErpTableName();
				tableName_right = tableRelation.getDwTableName();
				example_left.clear();
				example_left.or().andOnlineUatTestEqualTo(env_left).andTableNameEqualTo(tableName_left.toUpperCase());
				example_right.clear();
				example_right.or().andOnlineUatTestEqualTo(env_right).andTableNameEqualTo(tableName_right.toUpperCase());
				diff.addDataToCompare(tableName_left, 
						tableColumnInfoService.findByCondition(example_left), 
						tableName_right, 
						tableColumnInfoService.findByCondition(example_right));
				if (diff.getDiffMap().keySet().size() > 0) {
					diffList.add(diff);
				}
			}
		}
		result.setData(diffList);
		return result;
	}	
	
}
